﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DAL.EstRec;
using System.Data.Common;

namespace BAL.EstRec
{
    public class ListaCapturaPivot
    {
        private string Path;
        public List<CapturaPivotFDV> ListaElementos { get; set; }

        public ListaCapturaPivot(string Path)
        {
            ListaElementos = new List<CapturaPivotFDV>();
            this.Path = Path;
        }

        public void GenerarLista() {
            
            System.Data.DataTable dtExcel = new System.Data.DataTable();
            dtExcel.TableName = "MyExcelData";
            string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Path + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
            //string SourceConstr = @"Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + @";Extended Properties=""Excel 12.0 Macro;HDR= Yes;ImpoertMixedTypes=Text;TypeGuessRows=0""";
            //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xls;Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""
            //Provider=Microsoft.Jet.OLEDB.4.0;
            
           
            OleDbConnection con = new OleDbConnection(SourceConstr);
            
               string query = "Select * from [Sheet1$]";
            
            OleDbDataAdapter data = new OleDbDataAdapter(query, con);

            try
            {
                data.Fill(dtExcel);
            }
            catch (Exception)
            {
                data.Dispose();

                query = "Select * from [Hoja1$]";

                data = new OleDbDataAdapter(query, con);

                data.Fill(dtExcel);
                
            } 

            string Id_Recurso = "";

            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                string ID_POS = "";
                string cantidad = "";

                for (int j = 0; j < dtExcel.Columns.Count; j++)
                {                  
                    //System.Windows.MessageBox.Show(dtExcel.Rows[i][j].ToString());
                    if (i == 0 && j == 1) {

                        //System.Windows.MessageBox.Show(dtExcel.Rows[i][j].ToString());
                        Id_Recurso = dtExcel.Rows[i][j].ToString();
                    }
                    else if (dtExcel.Rows[i][j].ToString().Equals("Tiempo de implementación"))
                    {                        
                        return;
                    }
                    else if (j == 0 && i != 0) {

                        ID_POS = dtExcel.Rows[i][j].ToString();
                    }
                    else if (j == 1) {

                        cantidad = dtExcel.Rows[i][j].ToString();

                        DataTable dtt = SrvDB.ExecuteQuery("EXEC SP_GET_POS_WITH_ID @ID_POS = '"+ ID_POS + "'");
                        Pos objPos = new Pos(ID_POS, dtt.Rows[0][0].ToString());
                        
                        DataTable dtt2 = SrvDB.ExecuteQuery("EXEC SP_GET_RECURSO_WITH_ID @ID_RECURSO = '" + Id_Recurso + "'");
                        Recurso objRecurso = new Recurso(Id_Recurso, dtt2.Rows[0][0].ToString());

                        CapturaPivotFDV cpFDV = new CapturaPivotFDV(objPos, objRecurso, Int32.Parse(cantidad));
                        ListaElementos.Add(cpFDV);

                        //System.Windows.MessageBox.Show(cpFDV.pos.idPos + " " + cpFDV.pos.pos + " | " + cpFDV.recurso.idRecurso + " " + cpFDV.recurso.recurso + " | " + cpFDV.cantidad);
                    }
                }

            }

           
        }      
    }
}
